#!/usr/bin/env python
# -*- coding: utf-8 -*-
import mysql.connector
import uuid
from mysql.connector import pooling


def gen_user_corp_etc():
    # '''源数据库配置 (sysmandb)'''
    src_db = {
        # 'host': '10.1.213.159',
        # 'user': 'GTXS_user',
        # 'password': 'GTXS_user_JG123',
        'host': '10.99.81.155',
        'port': 8306,
        'user': 'root',
        'password': 'Lgmi6395',
        'database': 'jg_main'
    }

    # '''目标数据库配置 (jg_usercenter)'''
    dest_db = {
        'host': '10.99.81.155',
        'port': 8306,
        'user': 'jg_usercenter',
        'password': 'Jg_usercenter_20250312',
        'database': 'jg_usercenter'
    }

    try:
        # 创建数据库连接
        # src_conn = mysql.connector.connect(**src_db)
        src_connection_pool = pooling.MySQLConnectionPool(
            pool_name="srcpool",
            pool_size=32,
            pool_reset_session=True,
            **src_db
        )
        src_conn = src_connection_pool.get_connection()
        q_conn = src_connection_pool.get_connection()

        # dest_conn = mysql.connector.connect(**dest_db)
        dest_connection_pool = pooling.MySQLConnectionPool(
            pool_name="destpool",
            pool_size=5,
            pool_reset_session=True,
            **dest_db
        )
        dest_conn = dest_connection_pool.get_connection()

        src_cursor = src_conn.cursor(dictionary=True)
        dest_cursor = dest_conn.cursor()

        # 1. 查询源数据,注意数据范围
        src_cursor.execute("""
            select * from sys_user where deleted_tag = 'N' and user_type not in ('CARRIER', 'DRIVER', 'CONSUMER', 'SUPPLIER') and id <> '110' order by id
        """)

        # 2. 准备插入语句（根据实际表结构调整字段）

        # 3. 执行同步
        batch_size = 10000
        count = 0

        roles = query_default_role(dest_conn)
        for user in src_cursor:
            processed_user = dict(user)  # 创建原始数据的副本
            # 使用Python函数处理特定字段
            processed_user['enterprise_id'] = 'C' + str(user['id'])
            processed_user['enterprise_name'] = user['user_nickname']
            processed_user['user_id'] = user['id']

            if user is not None:
                user_type = user['user_type']
                if user_type == 'EMPLOYEE':  # 子账号级别，公司属于：甘肃酒钢集团宏兴钢铁股份有限公司 gfxs001，部门：销售部门
                    # update dept
                    # update_user_dept(user['id'], '1927532053339381762', dest_cursor)
                    # update_user_corp_dept(user['id'], '2000', '1946142875867197442', dest_cursor)
                    gen_sub_user(processed_user, dest_cursor)
                elif user_type == 'DRIVER':  # 属于企业-个人
                    processed_user['register_type'] = 'person'
                    processed_user['enterprise_type'] = 'Personal'  # 个人司机

                    # insert sys_corp、sys_corp_log、etc
                    # insert_corp_and_role(processed_user, roles, dest_cursor)
                    gen_driver(processed_user, roles, q_conn, dest_cursor)
                elif user_type == 'CONSUMER':  # 属于企业-企业，企业类型：客户，名称是自己
                    processed_user['register_type'] = 'corp'
                    processed_user['enterprise_type'] = 'Consumer'

                    # insert sys_corp、sys_corp_log、etc
                    insert_corp_and_role(processed_user, roles, dest_cursor)
                elif user_type == 'SUPPLIER':  # 属于企业-企业，企业类型：供应商，名称自己
                    processed_user['register_type'] = 'corp'
                    processed_user['enterprise_type'] = 'Supplier'

                    # insert sys_corp、sys_corp_log、etc
                    insert_corp_and_role(processed_user, roles, dest_cursor)
                elif user_type == 'CARRIER':  # 属于企业-企业，企业类型：承运商，名称是自己
                    processed_user['register_type'] = 'corp'
                    processed_user['enterprise_type'] = 'Carrier'

                    # insert sys_corp、sys_corp_log、etc
                    insert_corp_and_role(processed_user, roles, dest_cursor)
                elif user_type == 'DOORKEEPER':  # 子账号级别，公司属于：甘肃酒钢集团宏兴钢铁股份有限公司 gfxs001 ，部门：保卫处
                    # update dept
                    # update_user_dept(user['id'], '1943618450433310721', dest_cursor)
                    update_user_corp_dept(user['id'], '2000', '1946142922432360450', dest_cursor)
                elif user_type == 'OUTER':  # 当司机处理
                    processed_user['register_type'] = 'person'
                    processed_user['enterprise_type'] = 'Personal'  # 个人司机

                    gen_driver(processed_user, roles, q_conn, dest_cursor)
                elif user_type == 'INNER':  # 当内部职工处理
                    gen_sub_user(processed_user, dest_cursor)
                else: # 当内部职工处理
                    print('unknown ' + user_type)
                    gen_sub_user(processed_user, dest_cursor)
            else:
                print('does not exits this condition.')
                # processed_user['user_code'] = 'U' + user['id']
                # insert sys_user
                # user_id = insert_user(processed_user, dest_cursor)
                # processed_user['user_id'] = user_id

            count += 1
            print(count)

            # 分批提交
            if count % batch_size == 0:
                dest_conn.commit()
                print(f"已同步 {count} 条记录")

        dest_conn.commit()  # 提交剩余记录
        print(f"同步完成！总计: {count} 条记录")

    except Exception as err:
        print(f"数据库错误: {err}")
        dest_conn.rollback()
    finally:
        # 关闭连接
        if src_cursor: src_cursor.close()
        if dest_cursor: dest_cursor.close()
        if src_conn: src_conn.close()
        if dest_conn: dest_conn.close()


def query_corp(corp_code, dest_cursor):
    sql = "select * from sys_corp where corp_code = %s"

    dest_cursor.execute(sql, (corp_code,))

    rows = dest_cursor.fetchone()
    return rows


def query_default_role(dest_conn):
    try:
        cursor = dest_conn.cursor(dictionary=True)

        sql = "select * from sys_role where default_flag = %s"
        cursor.execute(sql, ('1',))

        row_dict = cursor.fetchall()
        return row_dict
    finally:
        if cursor: cursor.close()


def query_driver(user_name, src_conn):
    try:
        d_cursor = src_conn.cursor(dictionary=True)

        sql = "select * from truck_driver where truck_driver_id = %s"
        d_cursor.execute(sql, (user_name,))

        rows = d_cursor.fetchone()
        return rows
    finally:
        if d_cursor: d_cursor.close()


def query_user_app(app_code, user_code, dest_cursor):
    sql = "select * from sys_user_app where user_code = %s and app_code = %s"
    dest_cursor.execute(sql, (user_code, app_code,))
    rows = dest_cursor.fetchone()
    return rows


def query_user_role(role_id, user_id, dest_cursor):
    sql = "select * from sys_user_role where user_id = %s and role_id = %s"
    dest_cursor.execute(sql, (user_id, role_id,))
    rows = dest_cursor.fetchone()
    return rows


def gen_sub_user(processed_user, dest_cursor):
    user_id = processed_user['user_id']
    user_code = processed_user['user_code']
    # 1、update user corp & dept
    update_user_corp_dept(user_id, '2000', '1946142875867197442', dest_cursor)

    # 2、insert user app
    dicts = [{'app_code': 'main'}, {'app_code': 'ymt'}, {'app_code': 'uc'}]
    for c in dicts:
        user_app = query_user_app(c['app_code'], user_code, dest_cursor)
        if user_app is None:
            insert_user_app(c['app_code'], user_code, dest_cursor)

    # 3、 insert user default role
    role1 = query_user_role('101', user_id, dest_cursor)
    if role1 is None:
        insert_user_role(101, user_code, user_id, dest_cursor)

    role2 = query_user_role('102', user_id, dest_cursor)
    if role2 is None:
        insert_user_role(102, user_code, user_id, dest_cursor)


def gen_driver(processed_user, roles, src_conn, dest_cursor):
    user_name = processed_user['user_name']
    driver = query_driver(user_name=user_name, src_conn=src_conn)
    if driver is None:
        insert_corp_and_role(processed_user, roles, dest_cursor)
    else:
        print(f'driver {user_name} already.')


def insert_corp_and_role(processed_user, roles, dest_cursor):
    corp_code = processed_user['enterprise_id']
    corp = query_corp(corp_code, dest_cursor)
    if corp is not None:
        print(f'corp code: {corp_code} is already exists.')
        return
    corp_code1 = 'C' + processed_user['user_tel']
    corp = query_corp(corp_code1, dest_cursor)
    if corp is not None:
        print(f'corp code: {corp_code1} is already exists.')
        return
    # insert sys_corp
    insert_corp(processed_user, dest_cursor)
    # insert sys_corp_log
    insert_corp_log(processed_user, dest_cursor)

    # insert sys_depart
    dept_id = insert_dept(processed_user['enterprise_name'], processed_user['enterprise_id'], dest_cursor)
    # update user corp & dept
    update_user_corp_dept(processed_user['id'], corp_code, dept_id, dest_cursor)

    # insert corp_app_role
    insert_corp_app_role(processed_user, roles, dest_cursor)


def insert_corp(processed_user, dest_cursor):
    sql = """
                INSERT INTO sys_corp (id,
                                   corp_code,
                                   corp_name,
                                   user_code,
                                   linkman, -- 联系人
                                   linkman_mobile, -- 联系人电话
                                   corp_legalperson, -- 法人
                                   corp_legalperson_contact, -- 法人电话
                                   corp_legalperson_id_number, -- 法人证件号
                                   linkman_idnum, -- 联系人证件号
                                   union_society_code, -- 统一社会信用代码
                                   remark,
                                   deleted_tag,
                                   create_user,
                                   create_date,
                                   change_user,
                                   change_date,
                                   check_user_cd,
                                   check_date,
                                   check_state, -- pass
                                   register_type, -- person
                                   business_id,
                                   create_user_name,
                                   change_user_name,
                                   check_start_date,
                                   check_end_date,
                                   operation_type, -- insert
                                   submit_by,
                                   submit_by_name,
                                   submit_date,
                                   enterprise_type) 
                VALUES (
                    %(enterprise_id)s, %(enterprise_id)s, %(user_nickname)s, %(user_code)s, %(user_nickname)s, %(user_tel)s, 
                    %(user_nickname)s, %(user_tel)s, %(credit_id)s, null, null, 'sync', %(deleted_tag)s, 
                    'admin', NOW(), 'admin', NOW(), 'U000000000000001', NOW(), 'pass', %(register_type)s, null, '系统', '系统', 
                    null, null, 'insert', %(user_code)s, %(user_nickname)s, NOW(), %(enterprise_type)s
                )
            """

    dest_cursor.execute(sql, processed_user)


def insert_corp_log(processed_user, dest_cursor):
    sql = """
                INSERT INTO sys_corp_log (id, corp_id,
                                   corp_code,
                                   corp_name,
                                   user_code,
                                   linkman, -- 联系人
                                   linkman_mobile, -- 联系人电话
                                   corp_legalperson, -- 法人
                                   corp_legalperson_contact, -- 法人电话
                                   corp_legalperson_id_number, -- 法人证件号
                                   linkman_idnum, -- 联系人证件号
                                   union_society_code, -- 统一社会信用代码
                                   remark,
                                   deleted_tag,
                                   create_user,
                                   create_date,
                                   change_user,
                                   change_date,
                                   check_user_cd,
                                   check_date,
                                   check_state, -- pass
                                   register_type, -- corp
                                   business_id,
                                   create_user_name,
                                   change_user_name,
                                   check_start_date,
                                   check_end_date,
                                   operation_type, -- insert
                                   submit_by,
                                   submit_by_name,
                                   submit_date,
                                   enterprise_type) 
                VALUES (
                    %(guid)s, %(enterprise_id)s, %(enterprise_id)s, %(user_nickname)s, %(user_code)s, %(user_nickname)s, %(user_tel)s, 
                    %(user_nickname)s, %(user_tel)s, %(credit_id)s, null, null, 'sync', %(deleted_tag)s, 
                    'admin', NOW(), 'admin', NOW(), 'U000000000000001', NOW(), 'pass', %(register_type)s, null, '系统', '系统', 
                    null, null, 'insert', %(user_code)s, %(user_nickname)s, NOW(), %(enterprise_type)s
                )
            """
    processed_user['guid'] = str(uuid.uuid4())
    dest_cursor.execute(sql, processed_user)


def insert_dept(depart_name, corp_code, dest_cursor):
    id = str(uuid.uuid4())
    sql = """
        INSERT INTO sys_depart (id, depart_name, state, iz_leaf, create_date, corp_code, remark, deleted_tag)
        values (%(id)s, %(depart_name)s, '1', '0', NOW(), %(corp_code)s, 'sync', 'N')
    """
    dest_cursor.execute(sql, ({'id': id, 'depart_name': depart_name, 'corp_code': corp_code}))

    return id


def update_user_corp_dept(user_id, corp_code, dept_id, dest_cursor):
    print(f'update user corp_code = {corp_code} for user_id = {user_id}')
    sql = "UPDATE sys_user SET corp_code = %s, depart_ids = %s where id = %s;"
    dest_cursor.execute(sql, (corp_code, dept_id, user_id))


def update_user_dept(user_id, dept_id, dest_cursor):
    print(f'update user dept_id = {dept_id} for user_id = {user_id}')
    sql = "UPDATE sys_user SET depart_ids = %s where id = %s;"
    dest_cursor.execute(sql, (dept_id, user_id))


def insert_corp_app_role(processed_user, roles, dest_cursor):
    # print('insert_corp_app_role')
    corp_code = processed_user['enterprise_id']
    user_id = processed_user['user_id']
    user_code = processed_user['user_code']
    dicts = []
    d = {'app_code': 'uc', 'corp_type': 'uc_user', 'corp_code': corp_code}
    dicts.append(d)

    if processed_user['enterprise_type'] == 'Consumer':
        wl = {'app_code': 'wl', 'corp_type': 'wl_seller', 'corp_code': corp_code}
        dicts.append(wl)
        ck = {'app_code': 'ck', 'corp_type': 'ck_receiver', 'corp_code': corp_code}
        dicts.append(ck)

    if processed_user['enterprise_type'] == 'Carrier':
        wl = {'app_code': 'wl', 'corp_type': 'wl_carrier', 'corp_code': corp_code}
        dicts.append(wl)
        main = {'app_code': 'main', 'corp_type': 'main_carrier', 'corp_code': corp_code}
        dicts.append(main)

    if processed_user['enterprise_type'] == 'InSapEnterprise':
        wl = {'app_code': 'wl', 'corp_type': 'wl_shipper', 'corp_code': corp_code}
        dicts.append(wl)
        ck = {'app_code': 'ck', 'corp_type': 'ck_shipper', 'corp_code': corp_code}
        dicts.append(ck)
        main = {'app_code': 'main', 'corp_type': 'main_user', 'corp_code': corp_code}
        dicts.append(main)

    if processed_user['enterprise_type'] == 'Supplier':
        wl = {'app_code': 'wl', 'corp_type': 'wl_shipper', 'corp_code': processed_user['enterprise_id']}
        dicts.append(wl)
        # main = {'app_code': 'main', 'corp_type': 'main_user', 'corp_code': processed_user['enterprise_id']}
        # dicts.append(main)

    if processed_user['enterprise_type'] == 'Personal':  # 个人司机
        wl = {'app_code': 'wl', 'corp_type': 'wl_driver', 'corp_code': corp_code}
        dicts.append(wl)
        main = {'app_code': 'main', 'corp_type': 'main_driver', 'corp_code': corp_code}
        dicts.append(main)
        ymt = {'app_code': 'ymt', 'corp_type': 'ymt_driver', 'corp_code': corp_code}
        dicts.append(ymt)

    insert_sql = """
        INSERT INTO sys_corp_app_role (app_code, corp_type, corp_code) VALUES (%(app_code)s, %(corp_type)s, %(corp_code)s)
    """

    # user = query_user(dest_conn, processed_user['truck_driver_telephone'])
    for c in dicts:
        # sys_corp_app_role
        dest_cursor.execute(insert_sql, c)
        # sys_user_app
        insert_user_app(c['app_code'], user_code, dest_cursor)
        # sys_user_role
        insert_default_role(c, user_code, user_id, roles, dest_cursor)

    # insert_reg_user_role
    insert_user_role(102, user_code, user_id, dest_cursor)
    # insert_reg_user_role(processed_user, dest_cursor, dest_conn)


def insert_user_app(app_code, user_code, dest_cursor):
    # print('insert_user_app')
    insert_sql = """
        INSERT INTO sys_user_app(user_code, app_code) VALUES (%s, %s)
    """

    dest_cursor.execute(insert_sql, (user_code, app_code))


def insert_default_role(c, user_code, user_id, roles, dest_cursor):
    # print('insert_default_role')

    # roles = query_default_role(dest_conn)
    for role in roles:
        if role['app_code'] == c['app_code'] and role['role_group'] == c['corp_type']:
            insert_user_role(role['id'], user_code, user_id, dest_cursor)


def insert_user_role(role_id, user_code, user_id, dest_cursor):
    insert_sql = """
            INSERT INTO sys_user_role(id, role_id, user_code, user_id) VALUES (%s, %s, %s, %s)
        """
    dest_cursor.execute(insert_sql, (str(uuid.uuid4()), role_id, user_code, user_id))


if __name__ == "__main__":
    gen_user_corp_etc()
